﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GIMS.MODEL;

namespace KPIS.GERP.GIMS.DAL
{
    public sealed class PurchaseRequestItemDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public PurchaseRequestItemDAL()
        {

        }

        public IList<PurchaseRequestItemInfo> GetPurchaseRequestItem(int PcReqSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestItemInfo> infoList = new List<PurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_pr_seq", PcReqSeq);

                strSQL = "SELECT pri.*"
                            + ", ex.BGM_EXP_NAME"
                            + ", u.ICM_UNIT_NAME"
                            + ", ri.ICM_RES_ITEM_NAME"
                            + ", CASE WHEN ri.ICM_RES_ITEM_SEQ IS NULL THEN ex.BGM_EXP_NAME ELSE ri.ICM_RES_ITEM_NAME END AS ITEM_NAME"
                        + " FROM ICM_PURCHASE_REQUEST_ITEM pri"
                            + " LEFT JOIN BGM_MAS_EXPENSE ex ON pri.BGM_EXP_SEQ = ex.BGM_EXP_SEQ"
                            + " LEFT JOIN ICM_MAS_RESOURCE_ITEM ri ON pri.ICM_RES_ITEM_SEQ = ri.ICM_RES_ITEM_SEQ"
                            + " LEFT JOIN ICM_MAS_UNIT u ON ri.ICM_UNIT_SEQ = u.ICM_UNIT_SEQ"
                        + " WHERE pri.ICM_PR_SEQ = @icm_pr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestItemInfo info = new PurchaseRequestItemInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        info.Descr = dr[i]["ICM_PR_ITEM_DESCR"].ToString();
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["BGM_EXP_SEQ"]) ? null : (Nullable<int>)(dr[i]["BGM_EXP_SEQ"]));
                        infoExpense.Name = dr[i]["BGM_EXP_NAME"].ToString();
                        infoItem.Seq = (Convert.IsDBNull(dr[i]["ICM_RES_ITEM_SEQ"]) ? null : (Nullable<int>)(dr[i]["ICM_RES_ITEM_SEQ"]));
                        infoItem.Name = dr[i]["ICM_RES_ITEM_NAME"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_QTY"]) ? null : (Nullable<int>)(dr[i]["ICM_PR_ITEM_QTY"]));
                        infoUnit.Name = dr[i]["ICM_UNIT_NAME"].ToString();
                        info.OriginalQty = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_ORI_QTY"]) ? null : (Nullable<int>)(dr[i]["ICM_PR_ITEM_ORI_QTY"]));
                        info.Price = (Convert.IsDBNull(dr[i]["ICM_PR_ITEM_PRICE"]) ? null : (Nullable<double>)(dr[i]["ICM_PR_ITEM_PRICE"]));
                        info.ItemName = dr[i]["ITEM_NAME"].ToString();

                        info.InfoExpense = infoExpense;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;

                        infoList.Add(info);
                    }
                }
                return infoList;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<PurchaseRequestItemInfo> GetPurchaseRequestItem_EditPcReq(int PcReqSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestItemInfo> infoList = new List<PurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@icm_pr_seq", PcReqSeq);

                strSQL = "select ri.icm_pr_item_seq, ex.bgm_exp_seq, ex.bgm_exp_name" +
                        " , coalesce(i.icm_res_item_name, ex.bgm_exp_name) as item_name" +
                        " , coalesce( ri.icm_pr_item_descr) as icm_pr_item_descr" +
                        " , coalesce(u.icm_unit_name, 'รายการ') AS icm_unit_name" +
                        " , ri.icm_pr_item_ori_qty" +
                        " , cast(case when ri.icm_pr_item_min_price <> ri.icm_pr_item_max_price then concat( FORMAT(ri.icm_pr_item_min_price,2), ' - ' ,  FORMAT(ri.icm_pr_item_max_price,2)) else FORMAT(ri.icm_pr_item_min_price,2) end as char) as ori_price" +
                        " , ri.icm_pr_item_min_price" +
                        " , ri.icm_pr_item_max_price " +
                        " , ri.icm_pr_item_qty" +
                        " , ri.icm_pr_item_price " +
                        " from icm_purchase_request_item ri" +
                        " inner join bgm_mas_expense ex on ri.bgm_exp_seq  = ex.bgm_exp_seq" +
                        " left join icm_mas_resource_item i on ri.icm_res_item_seq = i.icm_res_item_seq" +
                        " left join icm_mas_unit u on i.icm_unit_seq = u.icm_unit_seq" +
                        " where icm_pr_seq = @icm_pr_seq";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestItemInfo info = new PurchaseRequestItemInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_pr_item_seq"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["bgm_exp_seq"]) ? null : (Nullable<int>)(dr[i]["bgm_exp_seq"]));
                        infoExpense.Name = dr[i]["bgm_exp_name"].ToString();
                        infoItem.Name = dr[i]["item_name"].ToString();
                        info.Descr = dr[i]["icm_pr_item_descr"].ToString();
                        infoUnit.Name = dr[i]["icm_unit_name"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["icm_pr_item_qty"]) ? null : (Nullable<int>)(dr[i]["icm_pr_item_qty"]));
                        info.OriginalQty = (Convert.IsDBNull(dr[i]["icm_pr_item_ori_qty"]) ? null : (Nullable<int>)(dr[i]["icm_pr_item_ori_qty"]));
                        info.Price = (Convert.IsDBNull(dr[i]["icm_pr_item_price"]) ? null : (Nullable<double>)(dr[i]["icm_pr_item_price"]));
                        info.OriginalPrice = dr[i]["ori_price"].ToString();
                        info.MinPrice = (Convert.IsDBNull(dr[i]["icm_pr_item_min_price"]) ? null : (Nullable<double>)(dr[i]["icm_pr_item_min_price"]));
                        info.MaxPrice = (Convert.IsDBNull(dr[i]["icm_pr_item_max_price"]) ? null : (Nullable<double>)(dr[i]["icm_pr_item_max_price"]));

                        info.InfoExpense = infoExpense;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;

                        infoList.Add(info);
                    }
                }
                return infoList;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<PurchaseRequestItemInfo> GetPurchaseRequestItemFromBid(int BidSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestItemInfo> infoList = new List<PurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@bid_seq", BidSeq);

                strSQL = "select ri.icm_pr_item_seq, ex.bgm_exp_seq, ex.bgm_exp_name" +
                        " , coalesce(i.icm_res_item_name, ex.bgm_exp_name) as item_name" +
                        " , coalesce( ri.icm_pr_item_descr) as icm_pr_item_descr" +
                        " , coalesce(u.icm_unit_name, 'รายการ') AS icm_unit_name" +
                        " , ri.icm_pr_item_ori_qty" +
                        " , cast(case when ri.icm_pr_item_min_price <> ri.icm_pr_item_max_price then concat( FORMAT(ri.icm_pr_item_min_price,2), ' - ' ,  FORMAT(ri.icm_pr_item_max_price,2)) else FORMAT(ri.icm_pr_item_min_price,2) end as char) as ori_price" +
                        " , ri.icm_pr_item_min_price" +
                        " , ri.icm_pr_item_max_price " +
                        " , ri.icm_pr_item_qty" +
                        " , ri.icm_pr_item_price " +
                        " from icm_purchase_request_item ri" +
                        " inner join bgm_mas_expense ex on ri.bgm_exp_seq  = ex.bgm_exp_seq" +
                        " left join icm_mas_resource_item i on ri.icm_res_item_seq = i.icm_res_item_seq" +
                        " left join icm_mas_unit u on i.icm_unit_seq = u.icm_unit_seq" +
                        " where ri.icm_pr_seq in"
                            + " (select icm_pr_seq "
                            + " from icm_bid"
                            + " where icm_bid_seq = @bid_seq)";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestItemInfo info = new PurchaseRequestItemInfo();
                        BgmExpenseInfo infoExpense = new BgmExpenseInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_pr_item_seq"]);
                        infoExpense.Seq = (Convert.IsDBNull(dr[i]["bgm_exp_seq"]) ? null : (Nullable<int>)(dr[i]["bgm_exp_seq"]));
                        infoExpense.Name = dr[i]["bgm_exp_name"].ToString();
                        infoItem.Name = dr[i]["item_name"].ToString();
                        info.Descr = dr[i]["icm_pr_item_descr"].ToString();
                        infoUnit.Name = dr[i]["icm_unit_name"].ToString();
                        info.Qty = (Convert.IsDBNull(dr[i]["icm_pr_item_qty"]) ? null : (Nullable<int>)(dr[i]["icm_pr_item_qty"]));
                        info.OriginalQty = (Convert.IsDBNull(dr[i]["icm_pr_item_ori_qty"]) ? null : (Nullable<int>)(dr[i]["icm_pr_item_ori_qty"]));
                        info.Price = (Convert.IsDBNull(dr[i]["icm_pr_item_price"]) ? null : (Nullable<double>)(dr[i]["icm_pr_item_price"]));
                        info.OriginalPrice = dr[i]["ori_price"].ToString();
                        info.MinPrice = (Convert.IsDBNull(dr[i]["icm_pr_item_min_price"]) ? null : (Nullable<double>)(dr[i]["icm_pr_item_min_price"]));
                        info.MaxPrice = (Convert.IsDBNull(dr[i]["icm_pr_item_max_price"]) ? null : (Nullable<double>)(dr[i]["icm_pr_item_max_price"]));

                        info.InfoExpense = infoExpense;
                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;

                        infoList.Add(info);
                    }
                }
                return infoList;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(IList<PurchaseRequestItemInfo> infoList)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                for (int i = 0; i < infoList.Count; i++)
                {
                    dbManager.CreateParameters(4);

                    dbManager.AddParameters(0, "@icm_pr_item_seq", infoList[i].Seq);
                    dbManager.AddParameters(1, "@icm_pr_item_descr", infoList[i].Descr);
                    dbManager.AddParameters(2, "@icm_pr_item_qty", infoList[i].Qty);
                    dbManager.AddParameters(3, "@icm_pr_item_price", infoList[i].Price);

                    strSQL = "UPDATE icm_purchase_request_item " +
                            " SET icm_pr_item_descr = @icm_pr_item_descr " +
                            " , icm_pr_item_qty = @icm_pr_item_qty " +
                            " , icm_pr_item_price = @icm_pr_item_price " +
                            " where icm_pr_item_seq = @icm_pr_item_seq";

                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public IList<PurchaseRequestItemInfo> GetPurchaseRequestItem(string search_text, int PrSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestItemInfo> infoList = new List<PurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@search_text", search_text);
                dbManager.AddParameters(1, "@icm_pr_seq", PrSeq);

                strSQL = "select pri.icm_pr_item_seq" +
                            " , coalesce(ri.icm_res_item_name, x.bgm_exp_name) as item_name " +
                            " , pri.icm_pr_item_descr " +
                            " , pri.icm_pr_item_qty " +
                            " , pri.icm_pr_item_ori_qty " +
                            " , coalesce(u.icm_unit_name, 'รายการ') as unit_name " +
                            " , pri.icm_pr_item_price " +
                        " from icm_purchase_request_item pri " +
                            " inner join bgm_mas_expense x on pri.bgm_exp_seq = x.bgm_exp_seq " +
                            " left join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq " +
                            " left join icm_mas_unit u on ri.icm_unit_seq = u.icm_unit_seq " +
                        " where pri.icm_pr_seq = @icm_pr_seq "
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0 "
                                    + " ELSE (ri.icm_res_item_name LIKE CONCAT('%', @search_text , '%')"
                                        + " OR pri.icm_pr_item_descr LIKE CONCAT('%', @search_text , '%')"
                                    + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestItemInfo info = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_pr_item_seq"]);
                        infoItem.Name = (dr[i]["item_name"]).ToString();
                        info.Descr = (dr[i]["icm_pr_item_descr"]).ToString();
                        info.Qty = Convert.ToInt32(dr[i]["icm_pr_item_qty"].ToString());
                        info.OriginalQty = Convert.ToInt32(dr[i]["icm_pr_item_ori_qty"].ToString());
                        infoUnit.Name = (dr[i]["unit_name"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["icm_pr_item_price"].ToString());

                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseRequestItemInfo> GetPurchaseRequestItemFromBidder(string search_text, int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestItemInfo> infoList = new List<PurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@search_text", search_text);
                dbManager.AddParameters(1, "@icm_bidder_seq", BidderSeq);

                strSQL = "select pri.icm_pr_item_seq" +
                            " , coalesce(ri.icm_res_item_name, x.bgm_exp_name) as item_name " +
                            " , pri.icm_pr_item_descr " +
                            " , pri.icm_pr_item_qty " +
                            " , pri.icm_pr_item_ori_qty " +
                            " , coalesce(u.icm_unit_name, 'รายการ') as unit_name " +
                            " , pri.icm_pr_item_price " +
                        " from icm_purchase_request_item pri " +
                            " inner join bgm_mas_expense x on pri.bgm_exp_seq = x.bgm_exp_seq " +
                            " left join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq " +
                            " left join icm_mas_unit u on ri.icm_unit_seq = u.icm_unit_seq " +
                        " where pri.icm_pr_seq in "
                            + " (select b.icm_pr_seq"
                                + " from icm_bidder bd"
                                    + " inner join icm_bid b on bd.icm_bid_seq = b.icm_bid_seq"
                                + " where bd.icm_bidder_seq = @icm_bidder_seq"
                            + ")"
                            + " AND CASE WHEN @search_text = '' THEN 0 = 0 "
                                    + " ELSE (ri.icm_res_item_name LIKE CONCAT('%', @search_text , '%')"
                                        + " OR pri.icm_pr_item_descr LIKE CONCAT('%', @search_text , '%')"
                                    + ")"
                            + " END";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestItemInfo info = new PurchaseRequestItemInfo();
                        ResourceMasterItemInfo infoItem = new ResourceMasterItemInfo();
                        MasterUnitInfo infoUnit = new MasterUnitInfo();

                        info.Seq = Convert.ToInt32(dr[i]["icm_pr_item_seq"]);
                        infoItem.Name = (dr[i]["item_name"]).ToString();
                        info.Descr = (dr[i]["icm_pr_item_descr"]).ToString();
                        info.Qty = Convert.ToInt32(dr[i]["icm_pr_item_qty"].ToString());
                        info.OriginalQty = Convert.ToInt32(dr[i]["icm_pr_item_ori_qty"].ToString());
                        infoUnit.Name = (dr[i]["unit_name"]).ToString();
                        info.Price = Convert.ToDouble(dr[i]["icm_pr_item_price"].ToString());

                        infoItem.InfoUnit = infoUnit;
                        info.InfoItem = infoItem;
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<PurchaseRequestItemInfo> GetPurchaseRequestItemListFromBidder(int BidderSeq)
        {
            string strSQL;
            DataSet ds = null;
            DataRowCollection dr = null;
            IList<PurchaseRequestItemInfo> infoList = new List<PurchaseRequestItemInfo>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@icm_bidder_seq", BidderSeq);

                strSQL = "select pri.icm_pr_item_seq" +
                            " , coalesce(ri.icm_res_item_name, x.bgm_exp_name) as item_name " +
                        " from icm_purchase_request_item pri " +
                            " inner join bgm_mas_expense x on pri.bgm_exp_seq = x.bgm_exp_seq " +
                            " left join icm_mas_resource_item ri on pri.icm_res_item_seq = ri.icm_res_item_seq " +
                        " where pri.icm_pr_seq in "
                            + " (select b.icm_pr_seq"
                                + " from icm_bidder bd"
                                    + " inner join icm_bid b on bd.icm_bid_seq = b.icm_bid_seq"
                                + " where bd.icm_bidder_seq = @icm_bidder_seq"
                            + ")";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dr = ds.Tables[0].Rows;
                if (dr.Count > 0)
                {
                    for (int i = 0; i < dr.Count; i++)
                    {
                        PurchaseRequestItemInfo info = new PurchaseRequestItemInfo();

                        info.Seq = Convert.ToInt32(dr[i]["ICM_PR_ITEM_SEQ"]);
                        info.ItemName = (dr[i]["ITEM_NAME"]).ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
                //return null;
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }
    }
}
